﻿@using System.Configuration
@using System.Data;
@using System.Data.SqlClient
@{
    Layout = "~/_SiteLayout.cshtml";
    Page.Title = "Export Claim Page";

    bool hasError = false;
    string errorMessage = string.Empty;
    bool hasSuccess = false;
    string successMessage = string.Empty;
    int rowNumber = 0;
    string claimType = string.Empty;

    KeppelLand.Entities.ReportingPeriod lockedReportingPeriod = null;
    KeppelLand.Entities.ReportingPeriod nextLockedReportingPeriod = null;
    List<KeppelLand.Entities.ClaimPeriod> claimPeriodList = null;
    System.Data.DataSet ds = new System.Data.DataSet("CurrentClaims");
    
    if (IsPost)
    {
        if (Request.Form["actionButton"] == "export")
        {
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                System.Data.SqlClient.SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "UPDATE ReportingPeriod SET Locked = 1 WHERE Id = (SELECT TOP 1 Id FROM ReportingPeriod WHERE LockedDate IS NULL ORDER BY EndDate);";
                conn.Open();
                cmd.ExecuteNonQuery();

                cmd.CommandText = @"
UPDATE  Claim
        SET ReportingPeriodId = (SELECT Id FROM reportingPeriod WHERE Locked = 1)
FROM    Claim c
WHERE   (c.Status = 6 OR c.Status = 5)
        AND c.ApproveDate <  (SELECT EndDate FROM reportingPeriod WHERE Locked = 1)
        AND c.ClaimDate < (SELECT CONVERT(DATETIME, SUBSTRING(CONVERT(VARCHAR,EndDate, 112), 1, 6)+'01', 112) FROM reportingPeriod WHERE Locked = 1);";
                cmd.ExecuteNonQuery();

                cmd.CommandText = @"
INSERT INTO ReportingPeriodClaimant (ReportingPeriodId, EntryOwner, ClaimPeriod)
SELECT  rp.Id, c.EntryOwner, 
CONVERT(DATETIME, SUBSTRING(CONVERT(VARCHAR, c.ClaimDate, 112), 1, 6) + '01', 112) AS ClaimPeriod
FROM    Claim c, ReportingPeriod rp
WHERE   rp.Locked = 1
        AND (c.Status = 6 OR c.Status = 5)
        AND c.ApproveDate < rp.EndDate 
        AND c.ClaimDate < (SELECT CONVERT(DATETIME, SUBSTRING(CONVERT(VARCHAR,EndDate, 112), 1, 6)+'01', 112) FROM reportingPeriod WHERE Locked = 1)
GROUP BY rp.Id, c.EntryOwner, CONVERT(DATETIME, SUBSTRING(CONVERT(VARCHAR, c.ClaimDate, 112), 1, 6) + '01', 112);";
                cmd.ExecuteNonQuery();
            }

        } // if (Request.Form["actionButton"] == "submit")
        
    } // if (IsPost)

    nextLockedReportingPeriod = KeppelLand.Controllers.ReportingPeriodController.GetNextReportingPeriod();
    lockedReportingPeriod = KeppelLand.Controllers.ReportingPeriodController.GetLockedReportingPeriod();

    if (lockedReportingPeriod != null)
    {
        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
        {
            System.Data.DataSet dsClaimPeriod = new DataSet("ClaimPeriod");
            System.Data.SqlClient.SqlCommand cmd = conn.CreateCommand();
            System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd);

            cmd.CommandText = @"
SELECT  DISTINCT 
        YEAR(rpc.ClaimPeriod) AS 'Year', 
        MONTH(rpc.ClaimPeriod) AS 'Month', 
        CONVERT(DATETIME, SUBSTRING(CONVERT(VARCHAR,rpc.ClaimPeriod,112), 1, 6) + '01') AS 'LabelDate'
FROM    ReportingPeriodClaimant rpc
WHERE   rpc.ReportingPeriodId = (SELECT Id FROM ReportingPeriod WHERE Locked = @Locked)
ORDER BY CONVERT(DATETIME, SUBSTRING(CONVERT(VARCHAR,rpc.ClaimPeriod,112), 1, 6) + '01') DESC";

            cmd.CommandText = @"
SELECT  DISTINCT 
        YEAR(rpc.ClaimPeriod) AS 'Year', 
        MONTH(rpc.ClaimPeriod) AS 'Month', 
        CONVERT(DATETIME, SUBSTRING(CONVERT(VARCHAR,rpc.ClaimPeriod,112), 1, 6) + '01') AS 'LabelDate', 
        COALESCE(hr.CNT, 0) AS 'CNT',
        COALESCE(tot.CNT, 0) AS 'TOTAL',
        COALESCE(tot.CNT, 0) - COALESCE(hr.CNT, 0) AS 'Cleared'
FROM    ReportingPeriodClaimant rpc
LEFT JOIN 
        (
            SELECT  DISTINCT
                    YEAR(c.ClaimDate) AS 'Year', 
                    MONTH(c.ClaimDate) AS 'Month', 
                    COUNT(c.Id) AS 'CNT'
            FROM    Claim c
            WHERE   Status = 5 
                    AND ReportingPeriodId = (SELECT Id FROM ReportingPeriod WHERE Locked = @Locked)
                    AND c.ApproveDate < (SELECT EndDate FROM ReportingPeriod WHERE Locked = @Locked)
            GROUP BY YEAR(c.ClaimDate), MONTH(c.ClaimDate)
        ) hr
        ON  YEAR(rpc.ClaimPeriod) = hr.Year
        AND MONTH(rpc.ClaimPeriod) = hr.Month
LEFT JOIN 
        (
            SELECT  DISTINCT
                    YEAR(c.ClaimDate) AS 'Year', 
                    MONTH(c.ClaimDate) AS 'Month', 
                    COUNT(c.Id) AS 'CNT'
            FROM    Claim c
            WHERE   ReportingPeriodId = (SELECT Id FROM ReportingPeriod WHERE Locked = @Locked)
                    AND c.ApproveDate < (SELECT EndDate FROM ReportingPeriod WHERE Locked = @Locked)
            GROUP BY YEAR(c.ClaimDate), MONTH(c.ClaimDate)
        ) tot
        ON  YEAR(rpc.ClaimPeriod) = tot.Year
        AND MONTH(rpc.ClaimPeriod) = tot.Month
WHERE   rpc.ReportingPeriodId = (SELECT Id FROM ReportingPeriod WHERE Locked = @Locked)
ORDER BY CONVERT(DATETIME, SUBSTRING(CONVERT(VARCHAR,rpc.ClaimPeriod,112), 1, 6) + '01') DESC;";

            cmd.Parameters.AddWithValue("@Locked", 1);
            //cmd.Parameters.AddWithValue("@ClaimStatus", KeppelLand.Entities.ClaimStatus.SUBMITTED_TO_HR);
            da.Fill(dsClaimPeriod, "ClaimPeriod");

            if (dsClaimPeriod.Tables["ClaimPeriod"].Rows.Count > 0)
            {
                claimPeriodList = dsClaimPeriod.Tables["ClaimPeriod"].AsEnumerable().Select(r => new KeppelLand.Entities.ClaimPeriod
                {
                    Year = r.Field<int>("Year"),
                    Month = r.Field<int>("Month"),
                    LabelDate = r.Field<DateTime>("LabelDate"),
                    Count = r.Field<int>("CNT"),
                    Total = r.Field<int>("TOTAL"),
                    Cleared = r.Field<int>("Cleared"),
                }).ToList();
            }
        }
    }
}

@if (!KeppelLand.Helpers.UserIdentityHelper.IsTrainer())
{
    @RenderPage("~/Shared/Access_Control.cshtml");
                                                 return;
}

<div class="container-fluid main-content" role="main">

    @RenderPage("~/Shared/Help_Tab.cshtml")

    @if (hasError)
    {
        <div class="alert alert-danger alert-dismissible" role="alert">
            <button type="button" class="close" data-dismiss="alert" aria-label="Close"><span aria-hidden="true">&times;</span></button>
            @Html.Raw(errorMessage)
        </div>
    }
    @if (hasSuccess)
    {
        <div class="alert alert-success alert-dismissible" role="alert">
            <button type="button" class="close" data-dismiss="alert" aria-label="Close"><span aria-hidden="true">&times;</span></button>
            @successMessage
        </div>
    }

    <h2>
        Export Claim
    </h2>
    
    <form method="post">


        
        @if (lockedReportingPeriod == null)
        {
        <p>
            Clicking the below export button will export claims as cutoff on @(nextLockedReportingPeriod.EndDate.ToString("MMM dd, yyyy")).
        </p>

        <p>
            <button type="submit" name="actionButton" value="export" class="btn btn-primary">Export Claim</button>
        </p>
        }
        else
        {

        <p>
            <button type="button" class="btn btn-disabled disabled">Export Claim</button>
        </p>
        }

    </form>

    @if (claimPeriodList != null)
    {
        <h3>Approved claims <small>(cut-off at @(lockedReportingPeriod.EndDate.ToString("dd-MMM-yyyy")))</small>
        </h3>

        <table class="table">
            <thead>
                <tr>
                    <th class="col-lg-1">No</th>
                    <th class="col-lg-3">Claim Period (Year-Month)</th>
                    <th class="col-lg-8">Details</th>
                </tr>
            </thead>
            <tbody>
                @foreach (KeppelLand.Entities.ClaimPeriod p in claimPeriodList)
                {
                <tr>
                    <td>@(++rowNumber).</td>
                    @if (rowNumber == 1)
                    {
                        claimType = "CUR";
                    }
                    else
                    {
                        claimType = "ARR";
                    }
                    <td>
                    @if (p.Count > 0)
                    {
                        @(p.LabelDate.ToString("yyyy-MMM").ToUpperInvariant() + ".csv") 
                        @(" (" + p.Count + " claim(s) uncleared)")
                    }
                    else
                    {
                        <a href="@VirtualPathUtility.ToAbsolute(       "~/Export-ClaimFile.cshtml?rpid=" + @lockedReportingPeriod.Id + "&mm=" + @p.LabelDate.ToString("yyyyMM01") + "&type=" + claimType)">@(p.LabelDate.ToString("yyyy-MMM").ToUpperInvariant() + ".csv")</a> 
                    }
                    </td>
                    <td><a href="@VirtualPathUtility.ToAbsolute("~/Export-ClaimFile-Detail.cshtml?rpid=" + @lockedReportingPeriod.Id + "&mm=" + @p.LabelDate.ToString("yyyyMM01") + "&type=" + claimType)">@(p.LabelDate.ToString("yyyy-MMM").ToUpperInvariant() + "-Details.csv")</a>
                        <span>(@p.Cleared claim(s) cleared)</span>
                    </td>
                    
                </tr>
                }
            </tbody>
        </table>
    }
    
</div>

<div class="modal fade" id="helpModal" tabindex="-1" role="dialog" aria-labelledby="helpModalLabel" aria-hidden="true">
    <div class="modal-dialog">
        <div class="modal-content">
            <div class="modal-header">
                <button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">&times;</span></button>
                <h4 class="modal-title" id="helpModalLabel">Help <small>(Export Claim)</small></h4>
            </div>
            <div class="modal-body">
                <p>
                    Export.<br />
                    To export claims approved within this cutoff period claims, follow the below instructions.
                </p>
                <div class="panel panel-default">
                    <div class="panel-heading">Instructions (How should this page work?)</div>
                    <div class="panel-body">
                        <p>
                            This page should be accessible only to HR personel (Lynette).<br />
                            Export claim will export claims for the current reporting period (if exists).<br />
                            If current reporting period do not exist, Export claim will export claims for the next reporting period.<br />
                        </p>

                        <p>
                            Export data should be segregated by year-month.
                        </p>

                        <ul>
                            <li>HR to click export</li>
                            <li>A modal dialog should display to confirm whether to continue action.</li>
                        </ul>
                    </div>
                </div>
            </div>
            <div class="modal-footer">
                <button type="button" class="btn btn-primary" id="helpModal_Close" data-dismiss="modal">Close</button>
            </div>
        </div>
    </div>
</div>

@section Scripts
{
    <script type="text/javascript" src="~/Scripts/kendo/2014.3.1119/kendo.ui.core.min.js"></script>
    <script type="text/javascript" src="~/Scripts/moment.js"></script>
    <script type="text/javascript" src="~/Scripts/moment-with-locales.js"></script>
    <script type="text/javascript">
    (function ($) {
        "use strict";
        $(document).ready(function () {
        });
    }(jQuery));

    </script>
}